//Pull starting-point data for parental info.
clear all
set more off

//Load parental links
odbc load, exec("select Kund_LopNr_personnr, LopNr_Far, LopNr_Mor  from dbo.Foraldrar") dsn("[Project]") clear
duplicates report
duplicates drop
rename Kund_LopNr_personnr LopNr_PersonNr
destring, replace
save "[Folder]\Foraldrar.dta", replace



odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, Fodelsear, Kon, Sun2000niva, KU1Ink, ForvInk, SjukRe, SenInvAr  from dbo.LISA1990_Individ") dsn("[Project]") clear
destring, replace
duplicates drop LopNr_PersonNr, force
//Change var names to year-specific version

foreach x of varlist KU1Ink ForvInk SjukRe {
replace `x' =`x'*1.508614052
}

foreach x of varlist KU1Ink ForvInk SjukRe SenInvAr {
rename `x' `x'_1990
}
//Save data to merge 
save  "[Folder]\ParVars_WidePanel.dta", replace


//Change variable names to year-specific; merge years into panel
forvalues i=1991/2013 {
odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, Fodelsear, Kon, Sun2000niva, KU1Ink, ForvInk, SjukRe, SenInvAr  from dbo.LISA`i'_Individ") dsn("[Project]") clear
destring, replace
duplicates drop LopNr_PersonNr, force

//CPI adjust because I take averages later. 2014 is base year.
matrix KPI = (1.379797535, 1.348924269, 1.289021382, 1.261529175, 1.23033752, 1.224570313, 1.21838321, 1.219805447, 1.214606742, 1.202493287, 1.17368027, 1.149156891, 1.127256383, 1.122815186, 1.118009986, 1.102983604, 1.079102268, 1.042846213, 1.046152306, 1.033052132, 1.006614649, 0.997740293, 0.99818506, 1)

scalar KPI_`i'=KPI[1,`i'-1990]
foreach x of varlist KU1Ink ForvInk SjukRe {
replace `x'=`x'*KPI_`i'
}

foreach x of varlist KU1Ink ForvInk SjukRe SenInvAr {
rename `x' `x'_`i'
}

//Merge into wide panel
merge 1:1 LopNr_PersonNr using "[Folder]\ParVars_WidePanel.dta"
drop _merge

save  "[Folder]\ParVars_WidePanel.dta", replace

}


odbc load, exec("select LopNr_PersonNr, LopNr_PeOrgNr, Fodelsear, Kon, Sun2000niva, KU1Ink, ForvInk, SjukRe, SenInvAr, Kommun  from dbo.LISA2014_Individ") dsn("[Project]") clear
destring, replace
duplicates drop LopNr_PersonNr, force


foreach x of varlist KU1Ink ForvInk SjukRe SenInvAr {
rename `x' `x'_2014
}

merge 1:1 LopNr_PersonNr using "[Folder]\ParVars_WidePanel.dta"
drop _merge

save  "[Folder]\ParVars_WidePanel.dta", replace



use "[Folder]\ParVars_WidePanel.dta"


//Add data on parental links
merge 1:1 LopNr_PersonNr using "[Folder]\Foraldrar.dta"
drop if _merge==2
drop _merge

//Add data on country of birth
merge 1:1 LopNr_PersonNr using  "[Folder]\TimeInv_Ind.dta", keepusing(FodelseL*)
drop if _merge==2
drop _merge


save  "[Folder]\ParVars_WidePanel.dta", replace



//Add country variables
merge m:1 FodelseLandnamn using "[Data Folder]\MacroData_ToMona_v1"

drop if _merge==2
drop _merge

save  "[Folder]\ParVars_WidePanel.dta", replace

